package com.ztc.ZtcCapitalIncome.dao;

import com.aqgj.util.BaseDaoImpl;
import com.aqgj.util.Pager;
import com.ztc.ZtcCapitalIncome.bean.ZtcCapitalIncome;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository("ZtcCapitalIncomeDao")
public class ZtcCapitalIncomeDao extends BaseDaoImpl<ZtcCapitalIncome> {

    /** 获取平台收支明细 -时间段搜索 */
    public Pager<HashMap<String,Object>> searchCapitalIncomeList(Integer pageNumber, Integer pageSize, String startDate, String endDate) {
        Pager<HashMap<String,Object>> pager=new Pager<>();
        pager.setPageSize(pageSize);
        pager.setCurrentPage(pageNumber);
        String sql="select id,money,income_money,type,order_id,remark,create_time from ztc_capital_income ";
        if(startDate!="" && endDate!=""){
            sql+="where create_time>='"+startDate+"' and create_time<='"+endDate+"' ";
        }
        sql+="order by create_time desc";
        HashMap<String,Object> map= this.listAndSumSqlPagQuery(sql,pageNumber,pageSize);
        pager.setRecordTotal(Integer.parseInt(map.get("sum").toString()));
        String [] stringArrays=new String[]{"id","money","incomeMoney","type","orderId","remark","createTime"};
        pager.setContent(this.getKeyList((List<Object[]>) map.get("data"),stringArrays));
        return pager;
    }

    public List<Object[]> getAllCapitalIncomeList() {
        String sql="select order_id,type,money,income_money,remark,create_time from ztc_capital_income order by create_time desc";
        return  this.currentSession().createNativeQuery(sql).getResultList();
    }

    /** 获取平台收益总额 */
     public Object getTotalCapitalIncome() {
        String sql="select sum(income_money) from ztc_capital_income";
        return  this.currentSession().createNativeQuery(sql).getSingleResult();
    }


    /**
     * 获取最新N条收益信息.
     * @param number 条数(6)
     * @return
     */
    public List<Map> getUpCapitalIncome(Integer number){
        String sql = "SELECT CASE z.`type` WHEN 1 THEN '购票收入' WHEN 2 THEN '退票支出' WHEN 3 THEN '违约金收益' WHEN 4 THEN '违约金出账' END AS type " +
                     ",z.income_money money,z.create_time,if(z.`type` in (1,3),1,0) isExit " +
                     "FROM ztc_capital_income z " +
                     "ORDER BY z.create_time DESC limit ?";
        return  (List<Map>)this.currentSession().createNativeQuery(sql).setParameter(1,number).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
    }

    /**
     * 根据年份获取该年度每月收益总额.
     * @param year  年份
     * @return  每月收益额map
     */
    public Map getCapitalTotal(Integer year){
        String sql="select    " +
                "ifnull(sum(case month(z.create_time) when '1' then z.income_money else 0 end),0) as jan,   " +
                "ifnull(sum(case month(z.create_time) when '2' then z.income_money else 0 end),0) as feb,   " +
                "ifnull(sum(case month(z.create_time) when '3' then z.income_money else 0 end),0) as mar,   " +
                "ifnull(sum(case month(z.create_time) when '4' then z.income_money else 0 end),0) as apr,   " +
                "ifnull(sum(case month(z.create_time) when '5' then z.income_money else 0 end),0) as may,   " +
                "ifnull(sum(case month(z.create_time) when '6' then z.income_money else 0 end),0) as jun,   " +
                "ifnull(sum(case month(z.create_time) when '7' then z.income_money else 0 end),0) as jul,   " +
                "ifnull(sum(case month(z.create_time) when '8' then z.income_money else 0 end),0) as aug,   " +
                "ifnull(sum(case month(z.create_time) when '9' then z.income_money else 0 end),0) as sep,   " +
                "ifnull(sum(case month(z.create_time) when '10' then z.income_money else 0 end),0) as octo, " +
                "ifnull(sum(case month(z.create_time) when '11' then z.income_money else 0 end),0) as nov,  " +
                "ifnull(sum(case month(z.create_time) when '12' then z.income_money else 0 end),0) as dece  " +
                "from ztc_capital_income z   " +
                "where year(z.create_time)=? ";
            return (Map)this.currentSession().createNativeQuery(sql).setParameter(1,year).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList().get(0);
    }

}